前回はSQL Serverが稼働するサーバーのHW構成とSW構成の把握を通じて主にSQL Serverの外の環境を確認しました。ここからはSQL Server自身の環境、すなわちSQL Serverの振る舞いを設定する動作パラメータを網羅的に確認していきます。
SQL Serverの動作パラメータを設定対象で分類すると以下の３つになります。
| 分類 | 解説 | 有効スコープ |
| データベースパラメータ | SQL Serverを介してそれぞれのデータベースに対して設定するパラメータです。 | データベース |
| インスタンスパラメータ | SQL Serverを介してSQL Serverインスタンスに対して設定するパラメータです。 | インスタンス |
| OSパラメータ | SQL Serverを介さずOSに対して設定するパラメータです。設定はインスタンスのスコープで有効です。 |
今回は前編として「データベースパラメータ」と「インスタンスパラメータ」を、次回後編では「OSパラメータ」を確認していきます。
なお、この記事で紹介する、ビュー、関数、プロシージャ、コマンドはそれぞれ対応するバージョンを表記します（ただしサービスパックによる拡張があるため、各バージョンの最新サービスパックの適用を前提とした表記です）。また、すべての実行例はSQL Server 2016 SP1 Enterprise Edition on Windows Server 2016 Datacenter環境での実行結果です。お使いの環境によってはこの記事の結果と相違がでる可能性に留意ください。
データベースに対して設定されたパラメータを確認する
ここではそれぞれのデータベースに対して設定されたパラメータを、「DBオプション」と「DBスコープオプション」の2つに細分して紹介します。
DBオプション：sys.databasesカタログビュー（SQL Server 2008 以降）
SELECT * FROM sys.databases
sys.databases はCREATE DATABASEやALTER DATABASEの実行時に指定したDBオプションの設定がデータベースごとに確認できます。その他にも、データベースの状態（例：トランザクションログの切り捨てができない理由など）や機能（例：CDCやクエリストアなど）の有効・無効といった情報もあわせて確認することができます。なお、いくつかのオプションはDATABASEPROPERTYEX 関数（引数に指定したオプションに対する設定値を返却します）によっても確認することができます。
以下にDBオプションの中で注目する機会が多い項目をいくつかピックアップして紹介します。
| オプション | 補足 |
| delayed_durability | コミットを遅延させることで性能向上を図るオプションです。ただしトランザクションの持続性とのトレードオフであることに注意して下さい。これはOracleやPostgreSQLの非同期コミットに相当します。 |
| is_auto_update_stats_async_on | 統計情報自動更新の遅延を制御するオプションです。 |
| is_mixed_page_allocation_on | 混合エクステントの利用を制御するオプションです。 |
| is_parameterization_forced | クエリに対する自動的なパラメータ化を制御するオプションです。 |
| is_read_committed_snapshot_on | トランザクションの排他モードを制御します。このオプションが有効な場合は、OracleやPostgreSQLと互換性のある排他制御モードで動作します。 |
| log_reuse_wait | トランザクションログ切り捨ての阻害要因を示す状態確認のためのオプションです。 |
| page_verify_option | ページ破損の検出レベルを制御します。既定値はCHECKSUMです。古いバージョンからアップグレードしている場合はこの設定値がTORN_PAGE_DETECTIONもしくはNONEである可能性があります。その場合CHECKSUMに変更することを推奨します。 |
| recovery_model | 復旧モデルを制御するオプションです。 |
| snapshot_isolation_state | is_read_committed_snapshot_onと同様にトランザクションの排他モードを制御するオプションです。 |
| target_recovery_time_in_seconds | 間接チェックポイントの実行頻度を制御するオプションです。 |
DBスコープオプション：sys.database_scoped_configurationsカタログビュー（SQL Server 2016 以降）
SELECT * FROM sys.database_scoped_configurations
sys.database_scoped_configurations はALTER DATABASE SCOPED CONFIGURATIONの実行時に指定したDBスコープオプションの設定が確認できます。このカタログは従来インスタンススコープで設定していたオプションのいくつかがSQL Server 2016からはデータベーススコープで設定できるようになったことに伴い追加されました。現時点で4つあり、いずれも性能チューニングに利用される興味深いオプションです。これらは今後の記事の中で紹介していきたいと考えています。
